from django.shortcuts import render

# Create your views here.
from django.http import HttpResponse
import numpy as np
from scipy.linalg import norm
from sklearn.feature_extraction.text import CountVectorizer, TfidfVectorizer
import jieba
import time
import re
import pymysql
import json
import math
import sys

# reload(sys)
#
# sys.setdefaultencoding('utf8')
punc = "！？｡?＂＃＄％＆＇（）＊＋，－／：；＜＝＞＠［＼］＾＿｀｛｜｝～｟｠｢｣､、〃》「」『』【】〔〕〖〗〘〙〚〛〜〝〞〟〰〾〿–—‘’‛“”„‟…‧﹏."

punc = punc
stoplist = {}.fromkeys([line.strip() for line in open("stoplist.txt",errors="ignore")])

#连接数据库
def mysqlConnect():
    # conn = pymysql.connect(host="localhost", user="root", passwd="", db="test", charset="utf8")
    conn = pymysql.connect(host="rm-uf68c81y9b94t4n18.mysql.rds.aliyuncs.com", user="qfclou", passwd="$qf@3_1415!!", db="group_boundpu", charset="utf8")
    cur = conn.cursor()
    return conn, cur

def runSql(sql):

    conn,cur = mysqlConnect()
    try:
        cur.execute(sql)
        conn.commit()
    except:
        raise
    finally:
        cur.close()
        conn.close()
#根据sql返回需要的数据
def sqlSelect(sql):

    conn, cur = mysqlConnect()
    try:
        cur.execute(sql)
    except:
        raise
        return []

    rows = cur.fetchall()
    return rows

#转换成固定格式
def formData(data):
    allData = []
    for dd in data:
        allData.append(dd[0])

    return allData

def cosine_similarity_tfidf(s1, s2):
    """
    计算两个句子的TFIDF余弦相似度
    :param s1:
    :param s2:
    :return:
    """
    vectorizer = TfidfVectorizer(tokenizer=lambda s: s.split())
    corpus = [s1, s2]
    vectors = vectorizer.fit_transform(corpus).toarray()
    return np.dot(vectors[0], vectors[1]) / (norm(vectors[0]) * norm(vectors[1]))



#根据问题返回答案
def index(request):
    #
    list_smi_score=[]

    # return HttpResponse(json.dumps([1]), content_type="application/json")
    query = request.POST.get("question","")
    addr = request.POST.get("addr", "")

    # return HttpResponse([query], content_type="application/json")
    # query = "借书的一般程序是什么"
    query_del_punc= re.sub(u"[%s]+" % punc, "", query)
    query_cut = jieba.cut(query_del_punc, cut_all=False)
    query_segs = [word for word in query_cut if word not in stoplist]
    qu_str= ' '.join(query_segs)


    sql = "select questionOne from bootBate where addr = '{}'".format(addr)
    data = formData(sqlSelect(sql))

    i=0

    for line in data:
        i=i+1
        line_del_punc = re.sub(u"[%s]+" % punc, "", line)
        line_cut = jieba.cut(line_del_punc, cut_all=False)
        an_segs = [word for word in line_cut if word not in stoplist]
        an_str = ' '.join(an_segs)
        score=cosine_similarity_tfidf(qu_str,an_str)
        list_smi_score.append((score,i))

    list_smi_score.sort(reverse=True)
    try:
        id = (max(list_smi_score)[1])
    except:
        return HttpResponse(json.dumps({
            "error": -1
        }), content_type="application/json")


    sql = "select * from bootBate where addr = '{}' limit {}".format(addr,id)

    data = sqlSelect(sql)

    return HttpResponse(json.dumps({
        "answer" : data[id - 1][2],
        "type":1
    }), content_type="application/json")


#记录用户问的所用问题
def recordQuestion(request):
    question = request.POST.get("question","")
    addr = request.POST.get("addr","")
    isRight = request.POST.get("isRight",0)
    sql = "insert into recordQuestion(question,isRight,addr) values('{}',{},'{}')".format(question,isRight,addr)
    # return HttpResponse([sql], content_type="application/json")
    runSql(sql)

    return HttpResponse(json.dumps({
        "success":"True"
    }), content_type="application/json")

# 用户提的问题列表
def newQuestionList(request):

    page = int(request.POST.get("page", 1))
    limit = int(request.POST.get("limit", 10))
    addr = request.POST.get("addr", "")

    allData = []

    start = (page - 1) * limit
    end = start + limit

    sql = "select question,isRight,createTime from recordQuestion where addr = '{}'  order by id desc limit {},{}".format(
        addr, start, end)

    # return HttpResponse([sql], content_type="application/json")
    data = sqlSelect(sql)

    for dd in data:
        temp = {
            "question": "",
            "state": "",
            "time": ""
        }

        temp["question"] = dd[0]
        temp["state"] = dd[1]

        time_local = time.localtime(dd[2])
        # 转换成新的时间格式(2016-05-05 20:28:54)
        dt = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
        temp["time"] = dt

        allData.append(temp)

    sql = "select count(1) from recordQuestion where addr = '{}'".format(addr)
    data = sqlSelect(sql)

    allNum = data[0][0]

    return HttpResponse(json.dumps(
        {
            "collection": allData,
            "pageInfo": {
                "allNum": allNum,
                "currentPage": page,
                "allPage": math.ceil(allNum / limit)
            }
        }
    ), content_type="application/json")


# 智能问答问题列表
def questionList(request):

    page = int(request.POST.get("page", 1))
    limit = int(request.POST.get("limit", 10))
    addr = request.POST.get("addr", "")
    # page = 1
    # limit = 10
    # addr  = "上海"

    allData = []

    start = (page - 1) * limit
    end = start + limit

    sql = "select * from bootBate where addr = '{}'  order by id desc limit {},{}".format(addr, start, end)

    # return HttpResponse([sql], content_type="application/json")
    data = sqlSelect(sql)

    for dd in data:
        temp = {
            "id": "",
            "question": "",
            "answer": "",
            "time": "",
            "state": ""
        }

        temp["id"] = dd[0]
        temp["question"] = dd[1]
        temp["answer"] = dd[2]
        temp["answer"] = dd[4]

        time_local = time.localtime(dd[3])
        # 转换成新的时间格式(2016-05-05 20:28:54)
        dt = time.strftime("%Y-%m-%d %H:%M:%S", time_local)
        temp["time"] = dt

        allData.append(temp)

    sql = "select count(1) from bootBate where addr = '{}'".format(addr)
    data = sqlSelect(sql)

    allNum = data[0][0]

    return HttpResponse(json.dumps(
        {
            "collection": allData,
            "pageInfo": {
                "allNum": allNum,
                "currentPage": page,
                "allPage": math.ceil(allNum / limit)
            }
        }
    ), content_type="application/json")

#修改智能问答
def updateIntelligent(request):
    id = request.POST.get("id", 1)
    question = request.POST.get("question", '')
    answer = request.POST.get("answer", "")
    state = request.POST.get("state", 0)

    sql = "update bootBate set questionOne='{}',answer='{}',state='{}' where id = {}".format(question,answer,state,id)
    runSql(sql)

    return HttpResponse(json.dumps({
        "success": "True"
    }), content_type="application/json")

#增加智能问答
def inserIntelligent(request):
    question = request.POST.get("question", '')
    answer = request.POST.get("answer", "")
    addr = request.POST.get("addr", "")

    createTime = int(time.time())
    sql = "insert into bootBate(questionOne,answer,addr,createTime) VALUES ('{}','{}','{}',{})".format(question, answer, addr,createTime)
    runSql(sql)

    return HttpResponse(json.dumps({
        "success": "True"
    }), content_type="application/json")

#常见问题
def questionListNew(request):
    addr = request.POST.get("addr", "上海")

    sql = "select questionOne from bootBate where addr = '{}' limit 10".format(addr)

    data = sqlSelect(sql)

    data = formQuestionListNew(data)

    return HttpResponse(json.dumps({
        "collections": data,
        "type":2
    }), content_type="application/json")

def formQuestionListNew(data):
    alldata = []
    for dd in data:
        json = {
            "question":""
        }
        json["question"] = dd[0]
        alldata.append(json)
    return alldata

# questionListNew(1)




